﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using Aulac.Srv.Db.Provider; using Aulac.Srv.Db.Connect;
using Aulac.Rsc.Ems; using Aulac.Rsc.Ems.Tags;
using Aulac.Global.Enums; using Aulac.Global.Data; using Aulac.Global.Common; using Aulac.Global.Xml;
using Aulac.Srv.Db.Common;
using System.Data;
using System.Data.Common;

namespace Aulac.Srv.Db.Data.Ems.MdcFiles.PntInses
{
    partial class PntInsPrvd
    {
        /* Code Date: 02/18/2011 by Thangnc */

        private XmlDataDocument FindPntToRegExamDb( string pPntName, string pInsNum)
        {
            /* Code Date: 02/18/2011 by Thangnc */

            if (ValueHelper.isEmpty(pPntName) && ValueHelper.isEmpty(pInsNum))
                return null;

            string sFilterInsNum = string.Format(" (? IS NULL OR p0.{0} =?)", tagItems.PntIns.MATHE);
            string sFilterPntName = string.Format(" AND (? IS NULL OR p0.{0} = ?)", tagItems.PntIns.HOTEN);

            sqlQuery = CreateQueryStringFindPntToReg();

            string sFilter = string.Format("{0}{1}", sFilterInsNum, sFilterPntName);
            string query = string.Format("{0} WHERE {1}",sqlQuery,sFilter);

            DataTable tbResult = mDbPrd.ExecuteReaderDataToTable(mDbCmd, query, pInsNum, pInsNum, pPntName, pPntName);
            if (!ValueHelper.isEmpty(tbResult))
            {
                
                XmlDataDocument xmlOut = mDbPrd.CreateXmlDocument("", "");
                XmlElement eLines = XmlProcess.AddVchLinesElm(ref xmlOut);
                DataTableProcess.GetValuesToChildElmXml(tbResult, mDbPrd.DbAulacConnection.CultureInfo, ref eLines);
                return xmlOut;
            }
            return null;
        }

        private string CreateQueryStringFindPntToReg()
        {
            /* Code Date: 02/18/2011 by Thangnc */

            string[] arrPnt = new string[] { tagMdcFiles.Pnt.PntPrkID, tagMdcFiles.Pnt.PntID, tagMdcFiles.Pnt.PntName, tagMdcFiles.Pnt.PntBirthday, tagMdcFiles.Pnt.PntBirthYear, tagMdcFiles.Pnt.PntAddr, tagMdcFiles.Pnt.PntRacePrkID, tagMdcFiles.Pnt.PntSexID, tagMdcFiles.Pnt.PntTownWardPrkID, tagMdcFiles.Pnt.PntDistrictPrkID, tagMdcFiles.Pnt.PntProvincePrkID, tagMdcFiles.Pnt.PntCountryPrkID, tagMdcFiles.Pnt.PntJobGrpPrkID, tagMdcFiles.Pnt.PntPlaceWork, tagMdcFiles.Pnt.PntPhone, tagMdcFiles.Pnt.InsNum, tagMdcFiles.Pnt.InsDate, tagMdcFiles.Pnt.InsExpireDate, tagMdcFiles.Pnt.PrvcInsID, tagMdcFiles.Pnt.HspInsID };
            string[] arrCntr = new string[] { tagItems.Countries.CountryID, tagItems.Countries.CountryName };
            string[] arrPrvc = new string[] { tagItems.Province.ProvinceID, tagItems.Province.ProvinceName };
            string[] arrDistric = new string[] { tagItems.District.DistrictID, tagItems.District.DistrictName };
            string[] arrTown = new string[] { tagItems.TownWard.TownWardID, tagItems.TownWard.TownWardName };
            string[] arrPntSex = new string[] { tagItems.Sex.SexName };
            string[] arrPntRace = new string[] { tagItems.Race.RaceID, tagItems.Race.RaceName };

            sqlQuery = string.Format("SELECT p0.{0} as {1}", tagItems.PntIns.HOTEN, tagMdcFiles.Pnt.PntName);
            sqlQuery += string.Format(", {0} = Case {1} WHEN 0 Then 2 WHEN 1 then 1 end", tagMdcFiles.Pnt.PntSexID, tagItems.PntIns.GIOITINH);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.NGAYSINH, tagMdcFiles.Pnt.PntBirthday);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.NAMSINH, tagMdcFiles.Pnt.PntBirthYear);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.MATHE, tagMdcFiles.Pnt.InsNum);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.GTRI_TU, tagMdcFiles.Pnt.InsDate);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.GTRI_DEN, tagMdcFiles.Pnt.InsExpireDate);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.TINH_KCB, tagMdcFiles.Pnt.PrvcInsID);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.MA_KCB, tagMdcFiles.Pnt.HspInsID);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.DIA_CHI, tagMdcFiles.Pnt.PntAddr);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.MATINH, tagMdcFiles.Pnt.PntProvinceID);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.MAHUYEN, tagMdcFiles.Pnt.PntDistrictID);
            sqlQuery += string.Format(", p0.{0} as {1}", tagItems.PntIns.MAXA, tagMdcFiles.Pnt.PntTownWardID);

            sqlQuery += string.Format(" FROM {0} p0 ", TableNames.Dm_PntIns);
          
            //sqlQuery += string.Format(" FROM {0} pnt INNER JOIN {1} cntr", TableNames.MdcFile_PntInfo, TableNames.Dm_Countries);
            //sqlQuery += string.Format(" ON pnt.{0} = cntr.{1}", tagMdcFiles.Pnt.PntCountryPrkID, tagItems.Countries.CountryPrkID);

            //sqlQuery += string.Format(" INNER JOIN {0} prvc ", TableNames.Dm_Province);
            //sqlQuery += string.Format(" ON pnt.{0} = prvc.{0}", tagMdcFiles.Pnt.PntProvincePrkID, tagItems.Province.ProvincePrkID);

            //sqlQuery += string.Format(" INNER JOIN {0} distr ", TableNames.Dm_District);
            //sqlQuery += string.Format(" ON pnt.{0} = distr.{1}", tagMdcFiles.Pnt.PntDistrictPrkID, tagItems.District.DistrictPrkID);

            //sqlQuery += string.Format(" INNER JOIN {0} town ", TableNames.Dm_TownWard);
            //sqlQuery += string.Format(" ON pnt.{0} = town.{1}", tagMdcFiles.Pnt.PntTownWardPrkID, tagItems.TownWard.TownWardPrkID);

            //sqlQuery += string.Format(" INNER JOIN {0} pntSex ", TableNames.Dm_SexTypes);
            //sqlQuery += string.Format(" ON pnt.{0} = pntSex.{1}", tagMdcFiles.Pnt.PntSexID, tagItems.Sex.SexID);

            
            return sqlQuery;
        }
     
    }
}
